/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.home.dao;

import com.home.common.Constants;
import com.home.common.utils.HibernateUtil;
import com.home.mapping.Account;
import com.home.mapping.AttributeGroup;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;

/**
 *
 * @author pnduy
 */
public class AttributeGroupDao extends BaseDao{
    public List<AttributeGroup> findAttrGroupByType(Integer type) {
        List<AttributeGroup> list = new ArrayList<AttributeGroup>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createSQLQuery("SELECT * FROM attribute_group ag WHERE ag.fk_scorecard_type = :idType ORDER BY ag.order ASC").addEntity(AttributeGroup.class).setParameter("idType", type);
            
            list = query.list();
            for (AttributeGroup ag : list) {
                Hibernate.initialize(ag.getAttributes());
            }
        } catch (Exception e) {
        } finally {
            session.close();
        }
        return list;
    }
    
    public List<Object[]> findPerOfCateReportWeek(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int year, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("SELECT scorecard.subject_name, attribute_group.attribute_group_name, (AVG(scorecard_group_attribute_result.score)/attribute_group.attribute_group_weight) as average ");
            } else {
                queryStr.append("SELECT agent.agent_name, attribute_group.attribute_group_name, (AVG(scorecard_group_attribute_result.score)/attribute_group.attribute_group_weight) as average ");
            }
            queryStr.append("FROM scorecard ");
            if (group == Constants.GROUP_TYPE_AGENT) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");
            } else {
                queryStr.append("LEFT JOIN subject ON scorecard.fk_subject = subject.id_subject ");
            }
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
                queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            }
            queryStr.append("LEFT JOIN scorecard_group_attribute_result ON scorecard.id_scorecard = scorecard_group_attribute_result.fk_scorecard ");
            queryStr.append("LEFT JOIN attribute_group ON scorecard_group_attribute_result.fk_attribute_group = attribute_group.id_attribute_group ");
            queryStr.append("WHERE ");
            queryStr.append("(scorecard.week BETWEEN :periodFrom AND :periodTo) ");
            queryStr.append("AND (scorecard.year = :year) ");
            queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (venture.id_venture = :ventureId) ");
            }
            if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, attribute_group.id_attribute_group ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, attribute_group.id_attribute_group ");
            } else if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (scorecard.fk_subject IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_subject, attribute_group.id_attribute_group ");
            } else {
                queryStr.append("AND (scorecard.fk_agent_AGENT IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_AGENT, attribute_group.id_attribute_group ");
            }

            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("ORDER BY scorecard.subject_name, scorecard.week ASC");
            } else {
                queryStr.append("ORDER BY agent.agent_name,scorecard.week ASC");
            }

            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("year", year);
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                query.setParameter("ventureId", ventureId);
            }
            query.setParameter("chanelId", chanelId);

            if (query.list().size() > 0) {
                list = query.list();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }

        return list;
    }
    
    public List<Object[]> findPerOfCateReportMonth(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int yearFrom, int yearTo, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("SELECT scorecard.subject_name, attribute_group.attribute_group_name, (AVG(scorecard_group_attribute_result.score)/attribute_group.attribute_group_weight) as average ");
            } else {
                queryStr.append("SELECT agent.agent_name, attribute_group.attribute_group_name, (AVG(scorecard_group_attribute_result.score)/attribute_group.attribute_group_weight) as average ");
            }
            queryStr.append("FROM scorecard ");
            if (group == Constants.GROUP_TYPE_AGENT) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");
            } else {
                queryStr.append("LEFT JOIN subject ON scorecard.fk_subject = subject.id_subject ");
            }
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
                queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            }
            queryStr.append("LEFT JOIN scorecard_group_attribute_result ON scorecard.id_scorecard = scorecard_group_attribute_result.fk_scorecard ");
            queryStr.append("LEFT JOIN attribute_group ON scorecard_group_attribute_result.fk_attribute_group = attribute_group.id_attribute_group ");
            queryStr.append("WHERE ");
            queryStr.append("(scorecard.month BETWEEN :periodFrom AND :periodTo) ");
            queryStr.append("AND (scorecard.year BETWEEN :yearFrom AND :yearTo) ");
            queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (venture.id_venture = :ventureId) ");
            }
            if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, attribute_group.id_attribute_group ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, attribute_group.id_attribute_group ");
            } else if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (scorecard.fk_subject IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_subject, attribute_group.id_attribute_group ");
            } else {
                queryStr.append("AND (scorecard.fk_agent_AGENT IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_AGENT, attribute_group.id_attribute_group ");
            }

            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("ORDER BY scorecard.subject_name, scorecard.month ASC");
            } else {
                queryStr.append("ORDER BY agent.agent_name,scorecard.month ASC");
            }

            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("yearFrom", yearFrom);
            query.setParameter("yearTo", yearTo);
            query.setParameter("chanelId", chanelId);
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                query.setParameter("ventureId", ventureId);
            }
            if (query.list().size() > 0) {
                list = query.list();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }

        return list;
    }
    
    public List<Object[]> findPerOfCateReportQuarter(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int yearFrom, int yearTo, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("SELECT scorecard.subject_name, attribute_group.attribute_group_name, (AVG(scorecard_group_attribute_result.score)/attribute_group.attribute_group_weight) as average ");
            } else {
                queryStr.append("SELECT agent.agent_name, attribute_group.attribute_group_name, (AVG(scorecard_group_attribute_result.score)/attribute_group.attribute_group_weight) as average ");
            }
            queryStr.append("FROM scorecard ");
            if (group == Constants.GROUP_TYPE_AGENT) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");
            } else {
                queryStr.append("LEFT JOIN subject ON scorecard.fk_subject = subject.id_subject ");
            }
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
                queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            }
            queryStr.append("LEFT JOIN scorecard_group_attribute_result ON scorecard.id_scorecard = scorecard_group_attribute_result.fk_scorecard ");
            queryStr.append("LEFT JOIN attribute_group ON scorecard_group_attribute_result.fk_attribute_group = attribute_group.id_attribute_group ");
            queryStr.append("WHERE ");
            queryStr.append("(scorecard.quarter BETWEEN :periodFrom AND :periodTo) ");
            queryStr.append("AND (scorecard.year BETWEEN :yearFrom AND :yearTo) ");
            queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (venture.id_venture = :ventureId) ");
            }
            if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, attribute_group.id_attribute_group ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, attribute_group.id_attribute_group ");
            } else if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (scorecard.fk_subject IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_subject, attribute_group.id_attribute_group ");
            } else {
                queryStr.append("AND (scorecard.fk_agent_AGENT IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_AGENT, attribute_group.id_attribute_group ");
            }

            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("ORDER BY scorecard.subject_name, scorecard.quarter ASC");
            } else {
                queryStr.append("ORDER BY agent.agent_name,scorecard.quarter ASC");
            }

            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("yearFrom", yearFrom);
            query.setParameter("yearTo", yearTo);
            query.setParameter("chanelId", chanelId);
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                query.setParameter("ventureId", ventureId);
            }
            if (query.list().size() > 0) {
                list = query.list();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }

        return list;
    }
}
